// Project: Salient
// http://salient.codeplex.com
// 
// Copyright 2010, Sky Sanders <sky at skysanders.net>
// Dual licensed under the MIT or GPL Version 2 licenses.
// http://salient.codeplex.com/license
// 
// Date: May 5 2010 

#region

using System;
using System.Collections.Specialized;
using System.Configuration;
using System.Configuration.Provider;
using System.Data.EntityClient;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Security.Permissions;
using System.Text;
using System.Web;
using System.Web.Profile;
using Salient.Web.DataAccess;
using Salient.Web.SQLite;
using Salient.Web.SQLite.Model;

#endregion

namespace Salient.Web.Profile
{
    ///<summary>
    ///</summary>
    [AspNetHostingPermission(SecurityAction.LinkDemand, Level = AspNetHostingPermissionLevel.Minimal)]
    [AspNetHostingPermission(SecurityAction.InheritanceDemand, Level = AspNetHostingPermissionLevel.Minimal)]
    public class SQLiteProfileProvider : ProfileProvider
    {
        #region Constants

        private const int ProfileAuthenticationOptionAll = 2;

        private const int ProfileAuthenticationOptionAnonymous = 0;

        private const int ProfileAuthenticationOptionAuthenticated = 1;

        #endregion

        #region Fields

        private string _applicationName;

        private string _entityConnectionString;

        #endregion

        #region Properties

        ///<summary>
        ///</summary>
        public string EntityConnectionString
        {
            get { return _entityConnectionString; }
        }

        /// <summary>
        /// The name of the application using the custom membership provider.
        /// </summary>
        /// <returns>
        /// The name of the application using the custom membership provider.
        /// </returns>
        /// <remarks>
        /// The ApplicationName is used by the Roles class to associate users and roles with different applications. This enables multiple applications to use the same database to store user and role information without running into conflicts between duplicate user names or role names. Multiple ASP.NET applications can use the same database by specifying the same value in the ApplicationName property. The ApplicationName property can be set programmatically, or it can be set declaratively in the configuration file for the Web application using the applicationName attribute.
        ///
        /// If no ApplicationName property is specified, we recommend that the ApplicationVirtualPath property value be used.
        /// </remarks>
        public override string ApplicationName
        {
            get
            {
                if (String.IsNullOrEmpty(_applicationName))
                {
                    _applicationName = ProviderUtility.GetDefaultAppName();
                }
                return _applicationName;
            }
            set
            {
                if (value != null && value.Length > 256)
                {
                    throw new ProviderException(SR.GetString(SR.PersonalizationProviderApplicationNameExceedMaxLength,
                                                             "256"));
                }
                _applicationName = value;
            }
        }

        #endregion

        #region Public Methods

        /// <summary>
        /// When overridden in a derived class, deletes all user-profile data for profiles in which the last activity date 
        /// occurred before the specified date.
        /// </summary>
        /// <returns>
        /// The number of profiles deleted from the data source.
        /// </returns>
        /// <param name="authenticationOption">One of the <see cref="T:System.Web.Profile.ProfileAuthenticationOption"/> values, 
        /// specifying whether anonymous, authenticated, or both types of profiles are deleted.</param>
        /// <param name="userInactiveSinceDate">A <see cref="T:System.DateTime"/> that identifies which user profiles are 
        /// considered inactive. If the <see cref="P:System.Web.Profile.ProfileInfo.LastActivityDate"/>  value of a user profile 
        /// occurs on or before this date and time, the profile is considered inactive.</param>
        /// <remarks>
        /// The DeleteInactiveProfiles method is used to remove unused profile data from the data source. Only data for the 
        /// applicationName specified in the configuration file is removed. The authenticationOption parameter specifies whether 
        /// only anonymous profiles, only authenticated profiles, or all profiles are searched. Profiles in which the 
        /// LastActivityDate occurs on or before the specified userInactiveSinceDate parameter value are deleted.
        /// </remarks>
        public override int DeleteInactiveProfiles(ProfileAuthenticationOption authenticationOption,
                                                   DateTime userInactiveSinceDate)
        {
            int totalRecord;
            ProfileInfoCollection inactive = GetAllInactiveProfiles(authenticationOption, userInactiveSinceDate, 0,
                                                                    int.MaxValue, out totalRecord);
            DeleteProfiles(inactive);
            return totalRecord;
        }

        /// <summary>
        /// When overridden in a derived class, deletes profile properties and information for the supplied list of profiles.
        /// </summary>
        /// <returns>
        /// The number of profiles deleted from the data source.
        /// </returns>
        /// <param name="profiles">A <see cref="T:System.Web.Profile.ProfileInfoCollection"/>  of information about profiles that 
        /// are to be deleted.</param>
        /// <remarks>
        /// The DeleteProfiles method deletes the profiles specified in the profiles parameter. Only data for the applicationName 
        /// specified in the configuration file is removed.
        /// 
        /// When implementing this method in a derived class, you can use the applicationName value and the UserName property of each 
        /// ProfileInfo object in the supplied ProfileInfoCollection to determine which profiles to delete from your data source.
        /// </remarks>
        public override int DeleteProfiles(ProfileInfoCollection profiles)
        {
            if (profiles == null)
            {
                throw new ArgumentNullException("profiles");
            }

            if (profiles.Count < 1)
            {
                throw new ArgumentException(
                    SR.GetString(
                        SR.ParameterCollectionEmpty,
                        "profiles"),
                    "profiles");
            }

            string[] usernames = new string[profiles.Count];

            int iter = 0;
            foreach (ProfileInfo profile in profiles)
            {
                usernames[iter++] = profile.UserName;
            }

            return DeleteProfiles(usernames);
        }

        /// <summary>
        /// When overridden in a derived class, deletes profile properties and information for profiles that match the supplied 
        /// list of user names.
        /// </summary>
        /// <returns>
        /// The number of profiles deleted from the data source.
        /// </returns>
        /// <param name="usernames">A string array of user names for profiles to be deleted.</param>
        /// <remarks>
        /// The DeleteProfiles method deletes the profiles specified in the usernames parameter. Only data for the applicationName 
        /// specified in the configuration file is removed.
        /// </remarks>
        public override int DeleteProfiles(string[] usernames)
        {
            ProviderUtility.CheckArrayParameter(ref usernames, true, true, true, 256, "usernames");

            int numProfilesDeleted = 0;

            using (
                SQLiteEntityConnectionHolder holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString)
                )
            {
                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    Application app = GetOrCreateApplication(ctx);
                    using (EntityTransaction tx = holder.Connection.BeginTransaction())
                    {
                        try
                        {
                            // doing this with linq against large sets (400 for instance) causes
                            // sqlite to croak with a stack overflow coming from the parser.
                            // so will just get ugly old school on they ass and do it one at a time
                            usernames.Select(u => u.ToLower()).ToList().ForEach(username =>
                                {
                                    UserProfileMergedView profileView =
                                        ctx.UsersProfileMergedView.FirstOrDefault(
                                            p => p.ApplicationId == app.ApplicationId && p.LoweredUserName == username);
                                    if (profileView != null)
                                    {
                                        ctx.DeleteObject(ctx.Profiles.First(p => p.UserId == profileView.UserId));
                                        ctx.SaveChanges();
                                        numProfilesDeleted++;
                                    }
                                });
                            tx.Commit();
                        }
                        catch
                        {
                            tx.Rollback();
                            //TODO: Log error
                            throw new ProviderException(SR.ProviderUNKNOWNFailure);
                        }
                    }
                }
            }

            return numProfilesDeleted;
        }

        /// <summary>
        /// When overridden in a derived class, retrieves profile information for profiles in which the last activity date 
        /// occurred on or before the specified date and the user name matches the specified user name.
        /// </summary>
        /// <returns>
        /// A <see cref="T:System.Web.Profile.ProfileInfoCollection"/> containing user profile information for inactive profiles 
        /// where the user name matches the supplied <paramref name="usernameToMatch"/> parameter.
        /// </returns>
        /// <param name="authenticationOption">One of the <see cref="T:System.Web.Profile.ProfileAuthenticationOption"/> values, 
        /// specifying whether anonymous, authenticated, or both types of profiles are returned.</param>
        /// <param name="usernameToMatch">The user name to search for.</param>
        /// <param name="userInactiveSinceDate">A <see cref="T:System.DateTime"/> that identifies which user profiles are 
        /// considered inactive. If the <see cref="P:System.Web.Profile.ProfileInfo.LastActivityDate"/> value of a user profile 
        /// occurs on or before this date and time, the profile is considered inactive.</param>
        /// <param name="pageIndex">The index of the page of results to return.</param>
        /// <param name="pageSize">The size of the page of results to return.</param>
        /// <param name="totalRecords">When this method returns, contains the total number of profiles.</param>
        /// <remarks>
        /// The FindInactiveProfilesByUserName method is used to retrieve profile information for unused user profiles for profiles 
        /// in which the user name matches the supplied usernameToMatch parameter. Only data for the applicationName specified in 
        /// the configuration file is returned. The authenticationOption parameter specifies whether only anonymous profiles, 
        /// only authenticated profiles, or all profiles are searched. Of the searched profiles, any profile with a LastActivityDate 
        /// that occurs on or before the specified userInactiveSinceDate parameter value is returned.
        /// 
        /// If your data source supports additional search capabilities, such as wildcard characters, you can provide more extensive 
        /// search capabilities for user names.
        /// 
        /// The results returned by FindInactiveProfilesByUserName are constrained by the pageIndex and pageSize parameters. The 
        /// pageSize parameter identifies the maximum number of ProfileInfo objects to return in the ProfileInfoCollection. The 
        /// pageIndex parameter identifies which page of results to return, where zero identifies the first page. The totalRecords 
        /// parameter is an out parameter that is set to the total number of inactive user profiles for the configured 
        /// applicationName, based on the authenticationOption and userInactiveSinceDate parameters. For example, if there are 13 
        /// users for the configured applicationName, and the pageIndex value is 1 with a pageSize of 5, the ProfileInfoCollection 
        /// returned would contain the sixth through the tenth inactive profiles returned. The totalRecords parameter would be set 
        /// to 13.
        /// </remarks>
        public override ProfileInfoCollection FindInactiveProfilesByUserName(
            ProfileAuthenticationOption authenticationOption, string usernameToMatch, DateTime userInactiveSinceDate,
            int pageIndex, int pageSize, out int totalRecords)
        {
            ProviderUtility.CheckParameter(ref usernameToMatch, true, true, false, 256, "username");

            ProviderUtility.VerifyPagingParameters(pageIndex, pageSize);
            totalRecords = 0;

            ProfileInfoCollection returnValue = new ProfileInfoCollection();
            int authOption = (int) authenticationOption; // cannot use enum in closure
            using (
                SQLiteEntityConnectionHolder holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString)
                )
            {
                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    DateTime inactiveDate = userInactiveSinceDate.ToUniversalTime();
                    string username = usernameToMatch.ToLower();
                    Application app = GetOrCreateApplication(ctx);
                    IQueryable<UserProfileMergedView> query =
                        ctx.UsersProfileMergedView.Where(
                            p =>
                            p.ApplicationId == app.ApplicationId &&
                            p.LoweredUserName.Contains(username) &&
                            p.LastActivityDate <= inactiveDate &&
                            (authOption == ProfileAuthenticationOptionAll ||
                             (authOption == ProfileAuthenticationOptionAnonymous && p.IsAnonymous) ||
                             (authOption == ProfileAuthenticationOptionAuthenticated && !p.IsAnonymous)));
                    totalRecords = query.Count();

                    query.OrderBy(p => p.UserName).Skip(pageSize*pageIndex).Take(pageSize).
                        ToList().ForEach(p =>
                                         returnValue.Add(new ProfileInfo(p.UserName, p.IsAnonymous,
                                                                         p.LastActivityDate.GetValueOrDefault(),
                                                                         p.LastUpdatedDate, p.DataSize)));
                }
            }
            return returnValue;
        }

        /// <summary>
        /// When overridden in a derived class, retrieves profile information for profiles in which the user name matches the 
        /// specified user names.
        /// </summary>
        /// <returns>
        /// A <see cref="T:System.Web.Profile.ProfileInfoCollection"/> containing user-profile information for profiles where the 
        /// user name matches the supplied <paramref name="usernameToMatch"/> parameter.
        /// </returns>
        /// <param name="authenticationOption">One of the <see cref="T:System.Web.Profile.ProfileAuthenticationOption"/> values, 
        /// specifying whether anonymous, authenticated, or both types of profiles are returned.</param>
        /// <param name="usernameToMatch">The user name to search for.</param>
        /// <param name="pageIndex">The index of the page of results to return.</param>
        /// <param name="pageSize">The size of the page of results to return.</param>
        /// <param name="totalRecords">When this method returns, contains the total number of profiles.</param>
        /// <remarks>
        /// The FindProfilesByUserName method returns a list of profile information for profiles in which the user name matches the 
        /// supplied usernameToMatch parameter. Only data for the applicationName specified in the configuration file is returned. 
        /// The authenticationOption parameter specifies whether only anonymous profiles, only authenticated profiles, or all 
        /// profiles are searched.
        /// 
        /// If your data source supports additional search capabilities, such as wildcard characters, you can provide more extensive 
        /// search capabilities for user names.
        /// 
        /// The results returned by FindProfilesByUserName are constrained by the pageIndex and pageSize parameters. The pageSize 
        /// parameter identifies the maximum number of ProfileInfo objects to return in the ProfileInfoCollection. The pageIndex 
        /// parameter identifies which page of results to return, where zero identifies the first page. The totalRecords parameter 
        /// is an out parameter that is set to the total number of inactive user profiles for the configured applicationName, based 
        /// on the authenticationOption and userInactiveSinceDate parameters. For example, if there are 13 users for the configured 
        /// applicationName, and the pageIndex value is 1 with a pageSize of 5, the ProfileInfoCollection returned would contain the 
        /// sixth through the tenth inactive profiles returned. The totalRecords parameter would be set to 13.
        /// </remarks>
        public override ProfileInfoCollection FindProfilesByUserName(ProfileAuthenticationOption authenticationOption,
                                                                     string usernameToMatch, int pageIndex, int pageSize,
                                                                     out int totalRecords)
        {
            ProviderUtility.VerifyPagingParameters(pageIndex, pageSize);
            totalRecords = 0;

            ProfileInfoCollection returnValue = new ProfileInfoCollection();
            int authOption = (int) authenticationOption; // cannot use enum in closure
            using (
                SQLiteEntityConnectionHolder holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString)
                )
            {
                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    string username = usernameToMatch.ToLower();
                    Application app = GetOrCreateApplication(ctx);
                    IQueryable<UserProfileMergedView> query =
                        ctx.UsersProfileMergedView.Where(
                            p =>
                            p.ApplicationId == app.ApplicationId &&
                            p.LoweredUserName.Contains(username) &&
                            (authOption == ProfileAuthenticationOptionAll ||
                             (authOption == ProfileAuthenticationOptionAnonymous && p.IsAnonymous) ||
                             (authOption == ProfileAuthenticationOptionAuthenticated && !p.IsAnonymous)));
                    totalRecords = query.Count();

                    query.OrderBy(p => p.UserName).Skip(pageSize*pageIndex).Take(pageSize).
                        ToList().ForEach(p =>
                                         returnValue.Add(new ProfileInfo(p.UserName, p.IsAnonymous,
                                                                         p.LastActivityDate.GetValueOrDefault(),
                                                                         p.LastUpdatedDate, p.DataSize)));
                }
            }
            return returnValue;
        }

        /// <summary>
        /// When overridden in a derived class, retrieves user-profile data from the data source for profiles in which the last 
        /// activity date occurred on or before the specified date.
        /// </summary>
        /// <returns>
        /// A <see cref="T:System.Web.Profile.ProfileInfoCollection"/> containing user-profile information about the inactive 
        /// profiles.
        /// </returns>
        /// <param name="authenticationOption">One of the <see cref="T:System.Web.Profile.ProfileAuthenticationOption"/> values, 
        /// specifying whether anonymous, authenticated, or both types of profiles are returned.</param>
        /// <param name="userInactiveSinceDate">A <see cref="T:System.DateTime"/> that identifies which user profiles are 
        /// considered inactive. If the <see cref="P:System.Web.Profile.ProfileInfo.LastActivityDate"/>  of a user profile occurs 
        /// on or before this date and time, the profile is considered inactive.</param>
        /// <param name="pageIndex">The index of the page of results to return.</param>
        /// <param name="pageSize">The size of the page of results to return.</param>
        /// <param name="totalRecords">When this method returns, contains the total number of profiles.</param>
        /// <remarks>
        /// The GetAllInactiveProfiles method is used to retrieve profile information for unused user profiles. Only data for the 
        /// applicationName specified in the configuration file is returned. The authenticationOption parameter specifies whether 
        /// only anonymous profiles, only authenticated profiles, or all profiles are searched. Of the searched profiles, any 
        /// profile with a LastActivityDate that occurs on or before the specified userInactiveSinceDate parameter value is 
        /// returned.
        /// 
        /// The results returned by GetAllInactiveProfiles are constrained by the pageIndex and pageSize parameters. The pageSize 
        /// parameter identifies the maximum number of ProfileInfo objects to return in the ProfileInfoCollection. The pageIndex 
        /// parameter identifies which page of results to return, where zero identifies the first page. The totalRecords parameter 
        /// is an out parameter that is set to the total number of inactive user profiles for the configured applicationName, based 
        /// on the authenticationOption and userInactiveSinceDate parameters. For example, if there are 13 users for the configured 
        /// applicationName, and the pageIndex value is 1 with a pageSize of 5, the ProfileInfoCollection returned would contain the 
        /// sixth through the tenth inactive profiles returned. The totalRecords parameter would be set to 13.
        /// </remarks>
        public override ProfileInfoCollection GetAllInactiveProfiles(ProfileAuthenticationOption authenticationOption,
                                                                     DateTime userInactiveSinceDate, int pageIndex,
                                                                     int pageSize, out int totalRecords)
        {
            ProviderUtility.VerifyPagingParameters(pageIndex, pageSize);
            totalRecords = 0;

            ProfileInfoCollection returnValue = new ProfileInfoCollection();
            int authOption = (int) authenticationOption; // cannot use enum in closure
            using (
                SQLiteEntityConnectionHolder holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString)
                )
            {
                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    DateTime inactiveDate = userInactiveSinceDate.ToUniversalTime();
                    Application app = GetOrCreateApplication(ctx);
                    IQueryable<UserProfileMergedView> query =
                        ctx.UsersProfileMergedView.Where(
                            p =>
                            p.ApplicationId == app.ApplicationId &&
                            p.LastActivityDate <= inactiveDate &&
                            (authOption == ProfileAuthenticationOptionAll ||
                             (authOption == ProfileAuthenticationOptionAnonymous && p.IsAnonymous) ||
                             (authOption == ProfileAuthenticationOptionAuthenticated && !p.IsAnonymous)));
                    totalRecords = query.Count();

                    query.OrderBy(p => p.UserName).Skip(pageSize*pageIndex).Take(pageSize).
                        ToList().ForEach(p =>
                                         returnValue.Add(new ProfileInfo(p.UserName, p.IsAnonymous,
                                                                         p.LastActivityDate.GetValueOrDefault(),
                                                                         p.LastUpdatedDate, p.DataSize)));
                }
            }
            return returnValue;
        }

        /// <summary>
        /// When overridden in a derived class, retrieves user profile data for all profiles in the data source.
        /// </summary>
        /// <returns>
        /// A <see cref="T:System.Web.Profile.ProfileInfoCollection"/> containing user-profile information for all profiles in 
        /// the data source.
        /// </returns>
        /// <param name="authenticationOption">One of the <see cref="T:System.Web.Profile.ProfileAuthenticationOption"/> values, 
        /// specifying whether anonymous, authenticated, or both types of profiles are returned.</param>
        /// <param name="pageIndex">The index of the page of results to return.</param>
        /// <param name="pageSize">The size of the page of results to return.</param>
        /// <param name="totalRecords">When this method returns, contains the total number of profiles.</param>
        /// <remarks>
        /// The GetAllProfiles method is used to retrieve profile information for all profiles. Only data for the applicationName 
        /// specified in the configuration file is returned. The authenticationOption parameter specifies whether only anonymous 
        /// profiles, only authenticated profiles, or all profiles are searched.
        /// 
        /// The results returned by GetAllInactiveProfiles are constrained by the pageIndex and pageSize parameters. The pageSize 
        /// parameter identifies the maximum number of ProfileInfo objects to return in the ProfileInfoCollection. The pageIndex 
        /// parameter identifies which page of results to return, where zero identifies the first page. The totalRecords parameter 
        /// is an out parameter that is set to the total number of inactive user profiles for the configured applicationName, 
        /// based on the authenticationOption and userInactiveSinceDate parameters. For example, if there are 13 users for the 
        /// configured applicationName, and the pageIndex value is 1 with a pageSize of 5, the ProfileInfoCollection returned would 
        /// contain the sixth through the tenth inactive profiles returned. The totalRecords parameter would be set to 13.
        /// </remarks>
        public override ProfileInfoCollection GetAllProfiles(ProfileAuthenticationOption authenticationOption,
                                                             int pageIndex, int pageSize, out int totalRecords)
        {
            ProviderUtility.VerifyPagingParameters(pageIndex, pageSize);
            totalRecords = 0;

            ProfileInfoCollection returnValue = new ProfileInfoCollection();
            int authOption = (int) authenticationOption; // cannot use enum in closure
            using (
                SQLiteEntityConnectionHolder holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString)
                )
            {
                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    Application app = GetOrCreateApplication(ctx);
                    IQueryable<UserProfileMergedView> query =
                        ctx.UsersProfileMergedView.Where(
                            p =>
                            p.ApplicationId == app.ApplicationId &&
                            (authOption == ProfileAuthenticationOptionAll ||
                             (authOption == ProfileAuthenticationOptionAnonymous && p.IsAnonymous) ||
                             (authOption == ProfileAuthenticationOptionAuthenticated && !p.IsAnonymous)));
                    totalRecords = query.Count();

                    query.OrderBy(p => p.UserName).Skip(pageSize*pageIndex).Take(pageSize).
                        ToList().ForEach(p =>
                                         returnValue.Add(new ProfileInfo(p.UserName, p.IsAnonymous,
                                                                         p.LastActivityDate.GetValueOrDefault(),
                                                                         p.LastUpdatedDate, p.DataSize)));
                }
            }
            return returnValue;
        }

        /// <summary>
        /// When overridden in a derived class, returns the number of profiles in which the last activity date occurred on or 
        /// before the specified date.
        /// </summary>
        /// <returns>
        /// The number of profiles in which the last activity date occurred on or before the specified date.
        /// </returns>
        /// <param name="authenticationOption">One of the <see cref="T:System.Web.Profile.ProfileAuthenticationOption"/> values, 
        /// specifying whether anonymous, authenticated, or both types of profiles are returned.</param>
        /// <param name="userInactiveSinceDate">A <see cref="T:System.DateTime"/> that identifies which user profiles are considered 
        /// inactive. If the <see cref="P:System.Web.Profile.ProfileInfo.LastActivityDate"/>  of a user profile occurs on or before 
        /// this date and time, the profile is considered inactive.</param>
        /// <remarks>
        /// The GetNumberOfInactiveProfiles method is used to retrieve a count of all unused user profiles. Only data for the 
        /// applicationName specified in the configuration file is returned. The authenticationOption parameter specifies whether 
        /// only anonymous profiles, only authenticated profiles, or all profiles are searched. Of the searched profiles, any 
        /// profile with a LastActivityDate that occurs on or before the specified userInactiveSinceDate parameter value is counted.
        /// </remarks>
        public override int GetNumberOfInactiveProfiles(ProfileAuthenticationOption authenticationOption,
                                                        DateTime userInactiveSinceDate)
        {
            int authOption = (int) authenticationOption; // cannot use enum in closure
            using (
                SQLiteEntityConnectionHolder holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString)
                )
            {
                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    DateTime inactiveDate = userInactiveSinceDate.ToUniversalTime();
                    Application app = GetOrCreateApplication(ctx);
                    IQueryable<UserProfileMergedView> query =
                        ctx.UsersProfileMergedView.Where(
                            p =>
                            p.ApplicationId == app.ApplicationId &&
                            p.LastActivityDate <= inactiveDate &&
                            (authOption == ProfileAuthenticationOptionAll ||
                             (authOption == ProfileAuthenticationOptionAnonymous && p.IsAnonymous) ||
                             (authOption == ProfileAuthenticationOptionAuthenticated && !p.IsAnonymous)));
                    return query.Count();
                }
            }
        }

        /// <summary>
        /// Returns the collection of settings property values for the specified application instance and settings property group.
        /// </summary>
        /// <returns>
        /// A <see cref="T:System.Configuration.SettingsPropertyValueCollection"/> containing the values for the specified 
        /// settings property group.
        /// </returns>
        /// <param name="context">A <see cref="T:System.Configuration.SettingsContext"/> 
        /// describing the current application use.</param>
        /// <param name="collection">A <see cref="T:System.Configuration.SettingsPropertyCollection"/> containing the settings 
        /// property group whose values are to be retrieved.</param>
        /// <remarks>
        /// The GetPropertyValues method must be implemented to handle special settings, those marked with SpecialSettingAttribute, 
        /// as well as reconcile application and user settings.
        /// </remarks>
        public override SettingsPropertyValueCollection GetPropertyValues(SettingsContext context,
                                                                          SettingsPropertyCollection collection)
        {
            SettingsPropertyValueCollection svc = new SettingsPropertyValueCollection();

            if (collection.Count < 1)
                return svc;

            string username = (string) context["UserName"];

            foreach (SettingsProperty prop in collection)
            {
                if (prop.SerializeAs == SettingsSerializeAs.ProviderSpecific)
                    if (prop.PropertyType.IsPrimitive || prop.PropertyType == typeof (string))
                        prop.SerializeAs = SettingsSerializeAs.String;
                    else
                        prop.SerializeAs = SettingsSerializeAs.Xml;

                svc.Add(new SettingsPropertyValue(prop));
            }
            if (!String.IsNullOrEmpty(username))
            {
                string[] names = null;
                string values = null;
                byte[] buf = null;

                using (
                    SQLiteEntityConnectionHolder holder =
                        SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString))
                {
                    using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                    {
                        Application app = GetOrCreateApplication(ctx);
                        AspUserView userV =
                            ctx.AspUsersView.FirstOrDefault(
                                u => u.ApplicationId == app.ApplicationId && u.LoweredUserName == username.ToLower());
                        if (userV != null)
                        {
                            SQLite.Model.Profile upV = ctx.Profiles.FirstOrDefault(p => p.UserId == userV.UserId);

                            if (upV != null)
                            {
                                names = upV.PropertyNames.Split(':');
                                values = upV.PropertyValuesString;
                                buf = upV.PropertyValuesBinary;
                            }
                        }
                    }
                }
                ParseDataFromDB(names, values, buf, svc);
            }
            return svc;
        }

        /// <summary>
        /// Initializes the provider.
        /// </summary>
        /// <param name="name">
        /// The friendly name of the provider.
        /// </param>
        /// <param name="config">
        /// A collection of the name/value pairs representing the provider-specific attributes specified in the configuration for this provider.
        /// </param>
        /// <exception cref="T:System.ArgumentNullException">
        /// The name of the provider is null.
        /// </exception>
        /// <exception cref="T:System.ArgumentException">
        /// The name of the provider has a length of zero.
        /// </exception>
        /// <exception cref="T:System.InvalidOperationException">
        /// An attempt is made to call 
        /// <see cref="M:System.Configuration.Provider.ProviderBase.Initialize(System.String,System.Collections.Specialized.NameValueCollection)" /> 
        /// on a provider after the provider has already been initialized.
        /// </exception>
        public override void Initialize(string name, NameValueCollection config)
        {
            if (config == null)
            {
                throw new ArgumentNullException("config");
            }

            if (name.Length < 1)
            {
                name = "SQLiteProfileProvider";
            }

            if (string.IsNullOrEmpty(config["description"]))
            {
                config.Remove("description");
                config.Add("description", SR.GetString(SR.ProfileSqlProviderDescription));
            }

            base.Initialize(name, config);


            _entityConnectionString = config["connectionStringName"];

            if (string.IsNullOrEmpty(_entityConnectionString))
            {
                throw new ProviderException(SR.GetString(SR.ConnectionNameNotSpecified));
            }

            SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString);

            _applicationName = config["applicationName"];

            if (string.IsNullOrEmpty(_applicationName))
            {
                _applicationName = ProviderUtility.GetDefaultAppName();
            }

            if (_applicationName.Length > 256)
            {
                throw new ProviderException(SR.GetString(SR.ProviderApplicationNameTooLong));
            }

            config.Remove("connectionStringName");
            config.Remove("applicationName");

            if (config.Count > 0)
            {
                string attribUnrecognized = config.GetKey(0);
                if (!String.IsNullOrEmpty(attribUnrecognized))
                {
                    throw new ProviderException(SR.GetString(SR.ProviderUnrecognizedAttribute, attribUnrecognized));
                }
            }

            using (
                SQLiteEntityConnectionHolder holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString)
                )
            {
                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    GetOrCreateApplication(ctx);
                }
            }
        }

        /// <summary>
        /// Sets the values of the specified group of property settings.
        /// </summary>
        /// <param name="context">A <see cref="T:System.Configuration.SettingsContext"/> 
        /// describing the current application usage.</param>
        /// <param name="collection">A <see cref="T:System.Configuration.SettingsPropertyValueCollection"/> representing the 
        /// group of property settings to set.</param>
        /// <remarks>
        /// ApplicationSettingsBase contains the Save method, which is called to persist the values of all of its settings 
        /// properties. This method enumerates through all the settings providers associated with its settings properties, 
        /// and calls the SetPropertyValues method for each SettingsProvider to perform the actual serialization operation.
        /// 
        /// The SetPropertyValues method should be implemented with security in mind:
        /// 
        /// Only fully trusted code should be allowed to update application settings. Partially trusted code should be allowed 
        /// to update only user application settings. Untrusted code is not typically allowed to update application settings.
        /// 
        /// Usage quotas should be considered to guard against resource attacks by partially trusted applications.
        /// </remarks>
        public override void SetPropertyValues(SettingsContext context, SettingsPropertyValueCollection collection)
        {
            string username = (string) context["UserName"];
            bool userIsAuthenticated = (bool) context["IsAuthenticated"];

            if (string.IsNullOrEmpty(username) || collection.Count < 1)
                return;

            string names = String.Empty;
            string values = String.Empty;
            byte[] buf = null;

            PrepareDataForSaving(ref names, ref values, ref buf, true, collection, userIsAuthenticated);

            if (names.Length == 0)
                return;

            using (
                SQLiteEntityConnectionHolder holder = SQLiteEntityConnectionHolder.GetConnection(_entityConnectionString)
                )
            {
                using (AspNetDbEntities ctx = new AspNetDbEntities(holder.Connection))
                {
                    Application app = GetOrCreateApplication(ctx);
                    using (EntityTransaction tx = holder.Connection.BeginTransaction())
                    {
                        try
                        {
                            AspUser user = ctx.AspUsers.FirstOrDefault(u => u.LoweredUserName == username.ToLower());
                            if (user == null)
                            {
                                user = new AspUser
                                    {
                                        Application = app,
                                        UserName = username,
                                        LoweredUserName = username.ToLower(),
                                        LastActivityDate = ProviderUtility.Now(),
                                        IsAnonymous = !userIsAuthenticated
                                    };
                                ctx.AddToAspUsers(user);
                                ctx.SaveChanges();
                            }
                            SQLite.Model.Profile profile = ctx.Profiles.FirstOrDefault(p => p.UserId == user.UserId);
                            if (profile == null)
                            {
                                user.Profile = new SQLite.Model.Profile();
                            }
                            user.Profile.PropertyNames = names;
                            user.Profile.PropertyValuesString = values;
                            user.Profile.PropertyValuesBinary = buf;
                            user.Profile.LastUpdatedDate = ProviderUtility.Now();
                            ctx.SaveChanges();
                            tx.Commit();
                        }
                        catch
                        {
                            tx.Rollback();
                            throw;
                        }
                    }
                }
            }
        }

        #endregion

        #region Private Methods

        private Application GetOrCreateApplication(AspNetDbEntities entities)
        {
            return ProviderUtility.GetOrCreateApplication(entities, ApplicationName);
        }

        [SecurityPermission(SecurityAction.Assert, Flags = SecurityPermissionFlag.SerializationFormatter)]
        private static void ParseDataFromDB(string[] names, string values, byte[] buf,
                                            SettingsPropertyValueCollection properties)
        {
            if (names == null || values == null || buf == null || properties == null)
                return;
            try
            {
                for (int iter = 0; iter < names.Length/4; iter++)
                {
                    string name = names[iter*4];
                    SettingsPropertyValue pp = properties[name];

                    if (pp == null) // property not found
                        continue;

                    int startPos = Int32.Parse(names[iter*4 + 2], CultureInfo.InvariantCulture);
                    int length = Int32.Parse(names[iter*4 + 3], CultureInfo.InvariantCulture);

                    if (length == -1 && !pp.Property.PropertyType.IsValueType) // Null Value
                    {
                        pp.PropertyValue = null;
                        pp.IsDirty = false;
                        pp.Deserialized = true;
                    }
                    if (names[iter*4 + 1] == "S" && startPos >= 0 && length > 0 && values.Length >= startPos + length)
                    {
                        pp.SerializedValue = values.Substring(startPos, length);
                    }

                    if (names[iter*4 + 1] == "B" && startPos >= 0 && length > 0 && buf.Length >= startPos + length)
                    {
                        byte[] buf2 = new byte[length];

                        Buffer.BlockCopy(buf, startPos, buf2, 0, length);
                        pp.SerializedValue = buf2;
                    }
                }
            }
                // ReSharper disable EmptyGeneralCatchClause
            catch
                // ReSharper restore EmptyGeneralCatchClause
            {
                // Eat exceptions
            }
        }

        [SecurityPermission(SecurityAction.Assert, Flags = SecurityPermissionFlag.SerializationFormatter)]
        private static void PrepareDataForSaving(ref string allNames, ref string allValues, ref byte[] buf,
                                                 bool binarySupported, SettingsPropertyValueCollection properties,
                                                 bool userIsAuthenticated)
        {
            StringBuilder names = new StringBuilder();
            StringBuilder values = new StringBuilder();

            MemoryStream ms = (binarySupported ? new MemoryStream() : null);
            try
            {
                bool anyItemsToSave = false;

                foreach (SettingsPropertyValue pp in properties)
                {
                    if (pp.IsDirty)
                    {
                        if (!userIsAuthenticated)
                        {
                            bool allowAnonymous = (bool) pp.Property.Attributes["AllowAnonymous"];
                            if (!allowAnonymous)
                                continue;
                        }
                        anyItemsToSave = true;
                        break;
                    }
                }

                if (!anyItemsToSave)
                    return;

                foreach (SettingsPropertyValue pp in properties)
                {
                    if (!userIsAuthenticated)
                    {
                        bool allowAnonymous = (bool) pp.Property.Attributes["AllowAnonymous"];
                        if (!allowAnonymous)
                            continue;
                    }

                    if (!pp.IsDirty && pp.UsingDefaultValue) // Not fetched from DB and not written to
                        continue;

                    int len, startPos = 0;
                    string propValue = null;

                    if (pp.Deserialized && pp.PropertyValue == null) // is value null?
                    {
                        len = -1;
                    }
                    else
                    {
                        object sVal = pp.SerializedValue;

                        if (sVal == null)
                        {
                            len = -1;
                        }
                        else
                        {
                            if (!(sVal is string) && !binarySupported)
                            {
                                sVal = Convert.ToBase64String((byte[]) sVal);
                            }

                            if (sVal is string)
                            {
                                propValue = (string) sVal;
                                len = propValue.Length;
                                startPos = values.Length;
                            }
                            else
                            {
                                byte[] b2 = (byte[]) sVal;
                                if (ms != null)
                                {
                                    startPos = (int) ms.Position;
                                    ms.Write(b2, 0, b2.Length);
                                    ms.Position = startPos + b2.Length;
                                }
                                len = b2.Length;
                            }
                        }
                    }

                    names.Append(pp.Name + ":" + ((propValue != null) ? "S" : "B") +
                                 ":" + startPos.ToString(CultureInfo.InvariantCulture) + ":" +
                                 len.ToString(CultureInfo.InvariantCulture) + ":");
                    if (propValue != null)
                        values.Append(propValue);
                }

                if (binarySupported)
                {
                    buf = ms.ToArray();
                }
            }
            finally
            {
                if (ms != null)
                    ms.Close();
            }
            allNames = names.ToString();
            allValues = values.ToString();
        }

        #endregion
    }
}